<%
class AdodbHelper

'最后修改日期：2015年1月15日22:16:43
'2015年1月15日22:19:23      全部重新格式化

'成员变量：连接对象
Private m_Conn   ''    As ADODB.Connection

'成员变量：SQL命令对象
Private m_Command ''   As ADODB.Command

'成员变量：连接字符串
Private m_ConnString 'As String

'成员变量：数据库文件路径
Private m_FilePath ''  As String

'成员变量：自身对象
Private m_Me       ''  As New AdodbHelper

'常量'
Private adCmdText
Private adUseNone
Private adUseServer
Private adUseClient
Private adParamUnknown
Private adParamInput
Private adParamOutput
Private adParamInputOutput
Private adParamReturnValue
Private vbArray
Private vbBoolean
Private vbByte
Private vbCurrency
Private vbDataObject
Private vbDate
Private vbDecimal
Private vbDouble
Private vbEmpty
Private vbError
Private vbInteger
Private vbLong
Private vbNull
Private vbObject
Private vbSingle
Private vbString
Private vbUserDefinedType
Private vbVariant

private adCmdUnknown 

private adCmdTable 
private adCmdStoredProc 
private adCmdFile 
private adCmdTableDirect 

private adEmpty 
private adTinyInt 
private adSmallInt 
private adInteger 
private adBigInt 
private adUnsignedTinyInt 
private adUnsignedSmallInt 
private adUnsignedInt 
private adUnsignedBigInt 
private adSingle 
private adDouble 
private adCurrency 
private adDecimal 
private adNumeric 
private adBoolean 
private adError 
private adUserDefined 
private adVariant 
private adIDispatch 
private adIUnknown 
private adGUID 
private adDate 
private adDBDate 
private adDBTime 
private adDBTimeStamp 
private adBSTR 
private adChar 
private adVarChar 
private adLongVarChar 
private adWChar 
private adVarWChar 
private adLongVarWChar 
private adBinary 
private adVarBinary 
private adLongVarBinary 
private adChapter 
private adFileTime 
private adPropVariant 
private adVarNumeric 
private adArray 



'规定：每个函数执行完成后，都必须清空m_Command，并且创建一个空的对象
'【类初始化、释放】
'<CSCM>
'--------------------------------------------------------------------------------
' Project    :       类库构建项目
' Procedure  :       Class_Initialize
' Description:       [type_description_here]
' Created by :       Project Administrator
' Machine    :       MYSOFT-SUNR01
' Date-Time  :       1-18-2015-18:19:56
'
' Parameters :
'--------------------------------------------------------------------------------
'</CSCM>
Private Sub Class_Initialize()
'    adCmdText = 2
'    adUseNone = 1
'    adUseServer = 2
     adUseClient = 3

     adParamUnknown = &H0000&
     adParamInput = &H0001&
     adParamOutput = &H0002&
     adParamInputOutput = &H0003&
     adParamReturnValue = &H0004&
     
     vbArray=8192
     vbBoolean=11
     vbByte=17
     vbCurrency=6
     vbDataObject=13
     vbDate=7
     vbDecimal=14
     vbDouble=5
     vbEmpty=0
     vbError=10
     vbInteger=2
     vbLong=3
     vbNull=1
     vbObject=9
     vbSingle=4
     vbString=8
     vbUserDefinedType=36
     vbVariant=12
     
     adCmdUnknown = &H0008&
     adCmdText = &H0001&
     adCmdTable = &H0002&
     adCmdStoredProc = &H0004&
     adCmdFile = &H0100&
     adCmdTableDirect = &H0200&
     
     adEmpty = 0 
     adTinyInt = 16 
     adSmallInt = 2 
     adInteger = 3 
     adBigInt = 20 
     adUnsignedTinyInt = 17 
     adUnsignedSmallInt = 18 
     adUnsignedInt = 19 
     adUnsignedBigInt = 21 
     adSingle = 4 
     adDouble = 5 
     adCurrency = 6 
     adDecimal = 14 
     adNumeric = 131 
     adBoolean = 11 
     adError = 10 
     adUserDefined = 132 
     adVariant = 12 
     adIDispatch = 9 
     adIUnknown = 13 
     adGUID = 72 
     adDate = 7 
     adDBDate = 133 
     adDBTime = 134 
     adDBTimeStamp = 135 
     adBSTR = 8 
     adChar = 129 
     adVarChar = 200 
     adLongVarChar = 201 
     adWChar = 130 
     adVarWChar = 202 
     adLongVarWChar = 203 
     adBinary = 128 
     adVarBinary = 204 
     adLongVarBinary = 205 
     adChapter = 136 
     adFileTime = 64 
     adPropVariant = 138 
     adVarNumeric = 139 
     adArray = &H2000
End Sub

'<CSCM>
'--------------------------------------------------------------------------------
' Project    :       类库构建项目
' Procedure  :       Class_Terminate
' Description:       [type_description_here]
' Created by :       Project Administrator
' Machine    :       MYSOFT-SUNR01
' Date-Time  :       1-18-2015-18:19:56
'
' Parameters :
'--------------------------------------------------------------------------------
'</CSCM>
Private Sub Class_Terminate()
    Set m_Conn = Nothing
    Set m_Command = Nothing
End Sub


private function getNewCommand()
	set getNewCommand=server.createobject("adodb.command")
end function

private function getNewConn()
	set getNewConn=server.createobject("adodb.connection")
end function
'【类属性】
'读取：数据库连接字符串
'<CSCM>
'--------------------------------------------------------------------------------
' Project    :       类库构建项目
' Procedure  :       ConnectionString
' Description:       [type_description_here]
' Created by :       Project Administrator
' Machine    :       MYSOFT-SUNR01
' Date-Time  :       1-18-2015-18:19:56
'
' Parameters :
'--------------------------------------------------------------------------------
'</CSCM>
Public Property Get ConnectionString() 'As String
    ConnectionString = m_ConnString
End Property

'设置：连接字符串
'<CSCM>
'--------------------------------------------------------------------------------
' Project    :       类库构建项目
' Procedure  :       ConnectionString
' Description:       [type_description_here]
' Created by :       Project Administrator
' Machine    :       MYSOFT-SUNR01
' Date-Time  :       1-18-2015-18:19:56
'
' Parameters :       vNewValue (String)
'--------------------------------------------------------------------------------
'</CSCM>
Public Property Let ConnectionString(ByVal vNewValue)
    m_ConnString = vNewValue
End Property

'类是否准备好，是否可以执行SQL
'<CSCM>
'--------------------------------------------------------------------------------
' Project    :       类库构建项目
' Procedure  :       IsReady
' Description:       [type_description_here]
' Created by :       Project Administrator
' Machine    :       MYSOFT-SUNR01
' Date-Time  :       1-18-2015-18:19:56
'
' Parameters :
'--------------------------------------------------------------------------------
'</CSCM>
Public Property Get IsReady()
    IsReady = IIf(Len(ConnectionString) > 0, True, False)
End Property

'【类方法】
'执行查询，返回离线记录集
'<CSCM>
'--------------------------------------------------------------------------------
' Project    :       类库构建项目
' Procedure  :       ExecQuery
' Description:       [type_description_here]
' Created by :       Project Administrator
' Machine    :       MYSOFT-SUNR01
' Date-Time  :       1-18-2015-18:19:55
'
' Parameters :       SqlStr (String)
'--------------------------------------------------------------------------------
'</CSCM>
Public Function ExecQuery(ByVal SqlStr)' As ADODB.Recordset

    Dim tempRes' As New Recordset

    Set m_Command = getNewCommand()
    Call OpenConn
    m_Command.ActiveConnection = m_Conn
    m_Command.CommandText = SqlStr
    Set tempRes = m_Command.Execute()
    tempRes.ActiveConnection = Nothing
    Call CloseConn
    Set ExecQuery = tempRes
    Set m_Command = Nothing
End Function

'执行参数化查询，返回离线记录集
'<CSCM>
'--------------------------------------------------------------------------------
' Project    :       类库构建项目
' Procedure  :       ExecParamQuery
' Description:       [type_description_here]
' Created by :       Project Administrator
' Machine    :       MYSOFT-SUNR01
' Date-Time  :       1-18-2015-18:19:55
'
' Parameters :       SqlStr (String)
'                    Params() (Variant)
'--------------------------------------------------------------------------------
'</CSCM>
Public Function ExecParamQuery(Byval SqlStr,ByVal Params())
    Dim tempRes' As New Recordset

    Dim i      '' As Long

    Set m_Command = getNewCommand()
    '打开连接
    Call OpenConn
    m_Command.ActiveConnection = m_Conn
    m_Command.CommandText = SqlStr
    m_Command.CommandType = adCmdText

    '设置参数
    With m_Command

        For Each param In Params

            Dim Para 'As ADODB.Parameter

            Set Para = .CreateParameter(CStr(i), GetVarType(param), adParamInput, LenB(param))
            Para.Value = param
            .Parameters.Append Para
        Next

    End With

    '获取执行后记录集
    Set tempRes = m_Command.Execute()
    '与数据库连接脱钩
    tempRes.ActiveConnection = Nothing
    '关闭数据库连接
    Call CloseConn
    '返回数据集对象引用
    Set ExecParamQuery = tempRes
    '清空命令对象
    Set m_Command = Nothing
End Function

'执行查询，返回影响行数
'<CSCM>
'--------------------------------------------------------------------------------
' Project    :       类库构建项目
' Procedure  :       ExecNonQuery
' Description:       [type_description_here]
' Created by :       Project Administrator
' Machine    :       MYSOFT-SUNR01
' Date-Time  :       1-18-2015-18:19:55
'
' Parameters :       SqlStr (String)
'--------------------------------------------------------------------------------
'</CSCM>
Public Function ExecNonQuery(ByVal SqlStr) 'As Long

    '定义影响行数变量
    Dim affectedRows 'As Long

    '创建绑定Command对象
    Set m_Command = getNewCommand()
    '打开连接
    Call OpenConn
    '绑定Command到数据库连接
    m_Command.ActiveConnection = m_Conn
    '设置SQL语句
    m_Command.CommandText = SqlStr
    '设置SQL类型
    m_Command.CommandType = adCmdText
    '获取执行后影响行数
    m_Command.Execute affectedRows
    '关闭数据库连接
    Call CloseConn
    '清空命令对象
    Set m_Command = Nothing
    '返回影响行数
    ExecNonQuery = affectedRows
End Function

'执行参数化查询，返回影响行数
'<CSCM>
'--------------------------------------------------------------------------------
' Project    :       类库构建项目
' Procedure  :       ExecParamNonQuery
' Description:       [type_description_here]
' Created by :       Project Administrator
' Machine    :       MYSOFT-SUNR01
' Date-Time  :       1-18-2015-18:19:55
'
' Parameters :       SqlStr (String)
'                    Params() (Variant)
'--------------------------------------------------------------------------------
'</CSCM>
Public Function ExecParamNonQuery(ByVal SqlStr, ByVal Params())'' As Long

    Dim i            'As Long

    Dim affectedRows 'As Long

    Set m_Command = getNewCommand()
    '打开连接
    Call OpenConn
    m_Command.ActiveConnection = m_Conn
    m_Command.CommandText = SqlStr
    m_Command.CommandType = adCmdText

    '设置参数
    With m_Command

        For Each param In Params

            Dim Para 'As ADODB.Parameter

            Set Para = .CreateParameter(CStr(i), GetVarType(param), adParamInput, LenB(param))
            Para.Value = param
            .Parameters.Append Para
        Next

    End With

    '获取执行后记录集
    m_Command.Execute affectedRows
    '关闭数据库连接
    Call CloseConn
    '清空命令对象
    Set m_Command = Nothing
    '返回影响行数
    ExecParamNonQuery = affectedRows
End Function

'根据文件路径设置连接字符串
'<CSCM>
'--------------------------------------------------------------------------------
' Project    :       类库构建项目
' Procedure  :       SetConnToFile
' Description:       [type_description_here]
' Created by :       Project Administrator
' Machine    :       MYSOFT-SUNR01
' Date-Time  :       1-18-2015-18:19:55
'
' Parameters :       FilePath (String)
'--------------------------------------------------------------------------------
'</CSCM>
Public Sub SetConnToFile(ByVal FilePath, Byval PassWord)
    m_ConnString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath(FilePath) & ";Persist Security Info=True;Jet OLEDB:Database Password=" & PassWord & ";"
End Sub

'释放离线记录集
'<CSCM>
'--------------------------------------------------------------------------------
' Project    :       类库构建项目
' Procedure  :       ReleaseRecordset
' Description:       [type_description_here]
' Created by :       Project Administrator
' Machine    :       MYSOFT-SUNR01
' Date-Time  :       1-18-2015-18:19:55
'
' Parameters :       dbRes (ADODB.Recordset)
'--------------------------------------------------------------------------------
'</CSCM>
Public Sub ReleaseRecordset(ByRef dbRes)
    Set dbRes = Nothing
End Sub

'将制定表数据从一个数据库文件插入到另一个数据库文件中
'<CSCM>
'--------------------------------------------------------------------------------
' Project    :       类库构建项目
' Procedure  :       TransData
' Description:       [type_description_here]
' Created by :       Project Administrator
' Machine    :       MYSOFT-SUNR01
' Date-Time  :       1-18-2015-18:19:55
'
' Parameters :       SourceDb (String)
'                    DestDb (String)
'                    tableNames() (Variant)
'--------------------------------------------------------------------------------
'</CSCM>
Public Sub TransData(ByVal SourceDb , _
                     ByVal DestDb , _
                     ByVal tableNames())

    Dim tableName 'As Variant

    m_Me.SetConnToFile SourceDb

    If UBound(tableNames) <= 0 Then

        Exit Sub

    End If

    For Each tableName In tableNames

        m_Me.ExecQuery "select * into [" & DestDb & "]." & tableName & " from " & tableName
    Next

End Sub

'从打开数据库文件，返回一个打开的数据库连接
'<CSCM>
'--------------------------------------------------------------------------------
' Project    :       类库构建项目
' Procedure  :       DbConnFromFile
' Description:       [type_description_here]
' Created by :       Project Administrator
' Machine    :       MYSOFT-SUNR01
' Date-Time  :       1-18-2015-18:19:55
'
' Parameters :       FilePath (String)
'--------------------------------------------------------------------------------
'</CSCM>
Public Function DbConnFromFile(ByVal FilePath) 'As ADODB.Connection

    Dim tmpConn 'As New ADODB.Connection
    set tmpConn = getNewConn()
    tmpConn.CursorLocation = adUseClient
    tmpConn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath & ";"
    Set DbConnFromFile = tmpConn
End Function

'执行参数化查询，返回首行，首列值
'<CSCM>
'--------------------------------------------------------------------------------
' Project    :       类库构建项目
' Procedure  :       ExecParamQueryScalar
' Description:       [type_description_here]
' Created by :       Project Administrator
' Machine    :       MYSOFT-SUNR01
' Date-Time  :       1-18-2015-18:19:55
'
' Parameters :       SqlStr (String)
'                    Params() (Variant)
'--------------------------------------------------------------------------------
'</CSCM>
Public Function ExecParamQueryScalar(ByVal SqlStr , _
                                     ByVal Params())'' As Variant

    Dim varResult 'As Variant

    Dim tmpRes    'As Recordset

    Set tmpRes = ExecParamQuery(SqlStr, Params)

    If tmpRes.RecordCount <= 0 Then
        Set ExecParamQueryScalar = Nothing
    Else
        ExecParamQueryScalar = tmpRes.Fields(0).Value
    End If

    ReleaseRecordset tmpRes
End Function

'执行查询，返回首行，首列值
'<CSCM>
'--------------------------------------------------------------------------------
' Project    :       类库构建项目
' Procedure  :       ExecQueryScalar
' Description:       [type_description_here]
' Created by :       Project Administrator
' Machine    :       MYSOFT-SUNR01
' Date-Time  :       1-18-2015-18:19:55
'
' Parameters :       SqlStr (String)
'--------------------------------------------------------------------------------
'</CSCM>
Public Function ExecQueryScalar(ByVal SqlStr) 'As Variant

    Dim varResult 'As Variant

    Dim tmpRes    'As Recordset

    Set tmpRes = ExecQuery(SqlStr)

    If tmpRes.RecordCount <= 0 Then
        Set ExecQueryScalar = Nothing
    Else
        ExecQueryScalar = tmpRes.Fields(0).Value
    End If

    ReleaseRecordset tmpRes
End Function

'【内部方法】
'打开数据库连接
'<CSCM>
'--------------------------------------------------------------------------------
' Project    :       类库构建项目
' Procedure  :       OpenConn
' Description:       [type_description_here]
' Created by :       Project Administrator
' Machine    :       MYSOFT-SUNR01
' Date-Time  :       1-18-2015-18:19:55
'
' Parameters :
'--------------------------------------------------------------------------------
'</CSCM>
Private Sub OpenConn()
    Set m_Conn = getNewConn()
    m_Conn.CursorLocation = adUseClient
    m_Conn.Open ConnectionString
End Sub

'关闭数据库连接
'<CSCM>
'--------------------------------------------------------------------------------
' Project    :       类库构建项目
' Procedure  :       CloseConn
' Description:       [type_description_here]
' Created by :       Project Administrator
' Machine    :       MYSOFT-SUNR01
' Date-Time  :       1-18-2015-18:19:55
'
' Parameters :
'--------------------------------------------------------------------------------
'</CSCM>
Private Sub CloseConn()
    m_Conn.Close
    Set m_Conn = Nothing
End Sub

'返回VB变量类型返回数据库参数的类型枚举值
'<CSCM>
'--------------------------------------------------------------------------------
' Project    :       类库构建项目
' Procedure  :       GetVarType
' Description:       [type_description_here]
' Created by :       Project Administrator
' Machine    :       MYSOFT-SUNR01
' Date-Time  :       1-18-2015-18:19:55
'
' Parameters :       Value (Variant)
'--------------------------------------------------------------------------------
'</CSCM>
Private Function GetVarType(ByRef Value) 'As DataTypeEnum

    Dim k
    set k=getNewCommand()

    Select Case VarType(Value)

        Case vbString
            GetVarType = adVariant 'adVarChar

        Case vbInteger
            GetVarType = adSmallInt

        Case vbBoolean
            GetVarType = adBoolean

        Case vbCurrency
            GetVarType = adCurrency

        Case vbDate
            GetVarType = adDate

        Case 8209
            GetVarType = adLongVarBinary

        Case Else
            GetVarType = adVariant
    End Select

End Function


end class
%>